SET NOCOUNT OFF

-- Default user credentials
DECLARE @projectName varchar(80)
DECLARE @admName SYSNAME
DECLARE @admPWD SYSNAME
DECLARE @usrName SYSNAME
DECLARE @usrPWD SYSNAME

DECLARE @adminsGroup SYSNAME
DECLARE @usersGroup SYSNAME
DECLARE @supportGroup SYSNAME

/*============================================================================*/
SET @projectName = N'Liberty'
SET @admPWD = N'5ar6a550'
SET @usrPWD = N'Adr1at1c'
/*============================================================================*/

/*============================================================================*/
--  The standard script follows and "Usually" does not need to be changed    
--  It performs the following:
--     1) Create an Admins Group <projectName>_Admins
--     2) Create a User Group    <projectName>_Users
--     3) Create a Support Group <projectName>_Support
--     4) Set <projectName>_Admins to be DB_OWNER
--     5) Set <projectName>_Support to be DB_DATAREADER
--     6) Create pid.<projectName>.Admin User, Grant access to Database
--        and join <projectName>_Admins
--     7) Create pid.<projectName> User, Grant access to Database
--        and join <projectName>_Users
-- 
/*============================================================================*/

-- Current Database
DECLARE @DB SYSNAME
SET @DB=DB_NAME()

-- Initialze Names based on projectName
--     Customize if desired

SET @admName = N'pid.' + @projectName + N'.Admin'
SET @usrName = N'pid.'  + @projectName
SET @adminsGroup = @projectName + N'_Admins'
SET @usersGroup = @projectName + N'_Users'
SET @supportGroup = @projectName + N'_Support'

/******************************************************************************/
/* Step 1. Create Admins group                                                */
/******************************************************************************/
PRINT @adminsGroup + ' group...'
IF NOT EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @adminsGroup)
BEGIN
  EXEC sp_addrole @adminsGroup
  PRINT ' - created OK'
END ELSE BEGIN
  PRINT ' - already exists'
END
  
/******************************************************************************/
/* Step 2. Create one group for users (SP Only)                               */
/******************************************************************************/
PRINT @usersGroup + ' group...'
IF NOT EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @usersGroup)
BEGIN
  EXEC sp_addrole @usersGroup
  PRINT ' - created OK'
END ELSE BEGIN
  PRINT ' - already exists'
END
 
/******************************************************************************/
/* Step 3. Create one group for Support                                       */
/******************************************************************************/
PRINT @supportGroup + ' group...'
IF NOT EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @supportGroup)
BEGIN
  EXEC sp_addrole @supportGroup
  PRINT ' - created OK'
END ELSE BEGIN
  PRINT ' - already exists'
END


/******************************************************************************/
/* Step 4. Admins group is DB_OWNER	                                          */
/******************************************************************************/
PRINT @adminsGroup + ' should be DB_OWNER...'
IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @adminsGroup)
BEGIN
  EXEC SP_ADDROLEMEMBER N'db_owner', @adminsGroup
  PRINT ' - ' + @adminsGroup + ' are DB Owners now.'
END ELSE BEGIN
  PRINT @adminsGroup + ' group does not exist. Make sure you have executed all DB creation scripts successfully or contact your DBA.'
END


/******************************************************************************/
/* Step 5. Support group is DB_DATAREADER                                     */
/******************************************************************************/
PRINT @supportGroup + ' should be DATAREADER...'
IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @supportGroup)
BEGIN
  EXEC SP_ADDROLEMEMBER N'db_datareader', @supportGroup
  PRINT ' - ' + @supportGroup + ' are DB DATAREADERS now.'
END ELSE BEGIN
  PRINT @supportGroup + ' group does not exist. Make sure you have executed all DB creation scripts successfully or contact your DBA.'
END

/*******************************************************************************/
/* Step 6. Create default SQL Login for Admins and put it in Admins role       */
/*******************************************************************************/
-- Create Login, if it does not exist
IF NOT EXISTS (SELECT * FROM MASTER.DBO.SYSLOGINS WHERE LOGINNAME = @admName)
BEGIN
  PRINT 'Creating Login ' + @admName + '...'
  EXEC SP_ADDLOGIN @admName, @admPWD, @DB
END ELSE BEGIN  
  PRINT 'Login ' + @admName + ' already exists.'
END

-- Grant access to the database
IF NOT EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @admName )
BEGIN
  PRINT 'Granting access to the current database to login ' + @admName + '...'
  -- Grant access to our database
  EXEC SP_GRANTDBACCESS @admName
END ELSE BEGIN  
  PRINT 'Login ' + @admName + ' already granted access to current database.'  
END  

-- Add this user to Admins role
PRINT 'Adding ' + @admName + ' to ' + @adminsGroup + ' group...'
EXEC SP_ADDROLEMEMBER @adminsGroup, @admName

/*******************************************************************************/
/* Step 7. Create default SQL Login for users and put it in Users role         */
/*******************************************************************************/
-- Create Login, if it does not exist
IF NOT EXISTS (SELECT * FROM MASTER.DBO.SYSLOGINS WHERE LOGINNAME = @usrName)
BEGIN
  PRINT 'Creating Login ' + @usrName + '...'
  EXEC SP_ADDLOGIN @usrName, @usrPWD, @DB
END ELSE BEGIN  
  PRINT 'Login ' + @usrName + ' already exists.'
END

-- Grant access to the database
IF NOT EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = @usrName )
BEGIN
  PRINT 'Granting access to the current database to login ' + @usrName + '...'
  -- Grant access to our database
  EXEC SP_GRANTDBACCESS @usrName
END ELSE BEGIN  
  PRINT 'Login ' + @usrName + ' already granted access to current database.'  
END  

-- Add this user to Users role
PRINT 'Adding ' + @usrName + ' to ' + @usersGroup  + ' group...'
EXEC SP_ADDROLEMEMBER @usersGroup, @usrName

/********************************************************************************/
/* Step 8. Create Message for Persistence Exception, if not yet created         */
/********************************************************************************/
IF NOT EXISTS( SELECT * FROM master..sysmessages WHERE error = 99999 )
BEGIN
	EXEC sp_addmessage @msgnum = 99999,
				@severity = 16, 
				@msgtext = '%s record was changed or removed by another process or user'
END

GO